#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库结构检查脚本
用于查看当前数据库中的表结构，分析是否满足Grafana可视化需求
"""

import pymysql
import sys
import os
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from config.db_config import GITLAB_DB_CONFIG, JENKINS_DB_CONFIG, FEISHU_DB_CONFIG

def check_database_structure(db_config, db_name):
    """检查数据库结构"""
    print(f"\n{'='*60}")
    print(f"检查数据库: {db_name}")
    print(f"{'='*60}")
    
    try:
        connection = pymysql.connect(**db_config)
        cursor = connection.cursor()
        
        # 获取所有表
        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()
        
        if not tables:
            print("❌ 数据库中没有表")
            return False
            
        print(f"✅ 发现 {len(tables)} 个表:")
        
        for table in tables:
            table_name = table[0]
            print(f"\n📋 表名: {table_name}")
            
            # 获取表结构
            cursor.execute(f"DESCRIBE {table_name}")
            columns = cursor.fetchall()
            
            print("   字段信息:")
            for column in columns:
                field_name = column[0]
                field_type = column[1]
                is_null = column[2]
                key = column[3]
                default = column[4]
                extra = column[5]
                
                key_info = ""
                if key == "PRI":
                    key_info = " [主键]"
                elif key == "UNI":
                    key_info = " [唯一]"
                elif key == "MUL":
                    key_info = " [索引]"
                    
                print(f"   - {field_name:<20} {field_type:<15} {'NULL' if is_null == 'YES' else 'NOT NULL':<8} {key_info}")
            
            # 获取表数据量
            cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
            count = cursor.fetchone()[0]
            print(f"   📊 数据量: {count} 条记录")
            
            # 如果有数据，显示最新的几条记录
            if count > 0:
                try:
                    cursor.execute(f"SELECT * FROM {table_name} LIMIT 3")
                    sample_data = cursor.fetchall()
                    print(f"   📄 样本数据 (前3条):")
                    for i, row in enumerate(sample_data, 1):
                        print(f"      第{i}条: {row}")
                except Exception as e:
                    print(f"   ⚠️  无法获取样本数据: {e}")
        
        cursor.close()
        connection.close()
        return True
        
    except Exception as e:
        print(f"❌ 连接数据库失败: {e}")
        return False

def analyze_grafana_requirements():
    """分析Grafana可视化需求"""
    print(f"\n{'='*60}")
    print("Grafana可视化需求分析")
    print(f"{'='*60}")
    
    requirements = {
        "基础可视化需求": [
            "提交趋势图 - 需要commits表的committed_date字段",
            "开发者活跃度 - 需要commits表的author_name字段",
            "项目统计 - 需要projects表的基础信息",
            "构建成功率 - 需要jenkins_builds表的status字段",
            "构建耗时分析 - 需要jenkins_builds表的duration字段"
        ],
        "高级可视化需求": [
            "代码质量趋势 - 需要commits表的stats_additions, stats_deletions字段",
            "团队协作分析 - 需要project_members表关联数据",
            "项目健康度 - 需要项目统计表project_statistics",
            "开发者排行榜 - 需要developer_statistics表",
            "问题处理分析 - 需要issues相关表"
        ]
    }
    
    for category, items in requirements.items():
        print(f"\n📊 {category}:")
        for item in items:
            print(f"   • {item}")

def main():
    """主函数"""
    print("🔍 GitLab数据同步工具 - 数据库结构检查")
    print("检查当前数据库是否满足Grafana可视化需求...")
    
    # 检查各个数据库
    databases = [
        (GITLAB_DB_CONFIG, "GitLab数据库"),
        (JENKINS_DB_CONFIG, "Jenkins数据库"),
        (FEISHU_DB_CONFIG, "飞书数据库")
    ]
    
    all_ok = True
    for db_config, db_name in databases:
        if not check_database_structure(db_config, db_name):
            all_ok = False
    
    # 分析Grafana需求
    analyze_grafana_requirements()
    
    print(f"\n{'='*60}")
    if all_ok:
        print("✅ 数据库结构检查完成!")
        print("💡 建议: 根据上述表结构编写对应的Grafana查询语句")
    else:
        print("❌ 部分数据库检查失败!")
        print("💡 建议: 先运行数据同步脚本创建表和导入数据")
    print(f"{'='*60}")

if __name__ == "__main__":
    main() 